Re: Database Encoding - Mailing list pgsql-novice

Hi Brad,

As far as I know the database encoding is set when you create the
database and can't be altered afterwards.  (I'm pretty sure of this
since it's written explicitly in the PostgreSQL 7.3.2 docs.)

It shouldn't be too hard to do the conversion by recreating your db
however (unless you had a really complicated one).  Perhaps someone
can suggest a tool to save your database structure and then re-apply
it on your new db.

With pgSQL4RB you can just use the COPY TO/FROM STDIN feature
(explained in the pgSQL4RB manual under backup/restore and in the
pgsql 7.3 docs) which should allow you to transfer all your data in a
few seconds time.  Just make sure you include OIDs if you're using
them for your relations, and don't rebuild your indexes till after
you have re-imported the data, otherwise they slow things down.

To convert your exported file from SQL_ASCII to UNICODE should be
really easy too with realbasic, except that I'm not sure which
encoding stands for SQL_ASCII.  If you only used English Roman
characters, then you wouldn't even have to convert your file though
and you could just upload it back into your new db.  If you need some
sample code for the encodings conversion, just drop me a note, but
it's just reading the file, converting, and writing it back out.
However, if you have bytea data, you shouldn't apply this conversion
on it and then you should export and import your data in 2
operations, keeping bytea separated.

Cheers,

Marc

PS: information about pgSQL4RB, which is a quite new RAD tool to
create PostgreSQL clients, can be found at
<http://aliacta.com/pgsql4rb.htm>.  I know Brad uses it.

>I have a few databases that set for ASCII encoding right now. Is
>there a way to convert them to Unicode? I tried using "alter
>database mConceptsEd set encoding='UNICODE' and about a hundred
>other minor variations on that theme, but I kept the error message
>that encoding wasn't a valid option. Am I stuck rebuilding the
>database from scratch or can I do a conversion? Thanks in advance
>for any help you can offer!
>
>--
>brad@truetech.org
>http://truetech.org
><><
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly